version 18.0               // version control
set processors 8           // to ensure replicability across different numbers of cores
clear all                  // clear existing data
macro drop _all            // and macros, clean slate
set seed 20220619          // set seed

*-------------------------------------------------------
* project: daylight saving time (dst)
*-------------------------------------------------------

local pgm  "dst-data02_solar_county_daily"  // file name
local who  "Muzhe Yang"                     // author
local dte  "2022-06-19"                     // created date
local dte2 "`c(current_date)'"              // last run date
local tag  "`pgm'.do, created by `who' on `dte', last run on `dte2'"

capture log close
log using "code\management\\`pgm'.txt", replace text
display "`tag'"

import excel "data_raw\solar\calendar_2018.xlsx", sheet("year 2018") firstrow clear // help datetime_software##r5
rename Date calendar_date
label variable calendar_date "calendar date"
sort calendar_date
codebook, compact
compress 
save "data_raw\solar\calendar_2018", replace 

use "data_clean\places\dst-data01_places_2020_release_eastern_central_clean", clear 
keep TractFIPS StateAbbr StateName CountyName CountyFIPS time_zone
tab time_zone, missing
drop if time_zone == "Central"
append using "data_clean\places\dst-data01_places_2020_release_central_mountain_clean", ///
       keep(TractFIPS StateAbbr StateName CountyName CountyFIPS time_zone)
tab time_zone, missing
drop if time_zone == "Mountain"
append using "data_clean\places\dst-data01_places_2020_release_mountain_pacific_clean", ///
       keep(TractFIPS StateAbbr StateName CountyName CountyFIPS time_zone)
tab time_zone, missing
codebook, compact
replace time_zone = "Central" if CountyFIPS == "12045" 
replace time_zone = "Pacific" if CountyFIPS == "06065"
replace time_zone = "Pacific" if CountyFIPS == "16049"
drop TractFIPS
duplicates drop
codebook, compact
gen GEOID = CountyFIPS
compare GEOID CountyFIPS
merge 1:1 GEOID using "data_clean\counties\dst-data01_counties_2020_clean", keepusing(county_lat county_lon)
drop if _merge == 2
drop GEOID _merge 
sort CountyFIPS
codebook, compact

cross using "data_raw\solar\calendar_2018"
sort CountyFIPS calendar_date
codebook, compact
assert !missing(calendar_date) // There should be no missing value in the "calendar_date" variable. 

* 03/11/2018 (Sunday): DST started, 2:00 AM became 3:00 AM
* 11/04/2018 (Sunday): DST ended, 2:00 AM became 1:00 AM 
* https://www.timeanddate.com/time/change/usa?year=2018 
gen     dst = 1 if mdy(3,11,2018) <= calendar_date & calendar_date < mdy(11,4,2018)
replace dst = 0 if calendar_date < mdy(3,11,2018) | calendar_date >= mdy(11,4,2018)
assert !missing(dst) // There should be no missing value in the "calendar_date" variable. 
gen     time_zone_offset = -4 if dst == 1 & time_zone == "Eastern"
replace time_zone_offset = -5 if dst == 1 & time_zone == "Central"
replace time_zone_offset = -6 if dst == 1 & time_zone == "Mountain"
replace time_zone_offset = -7 if dst == 1 & time_zone == "Pacific"
replace time_zone_offset = -7 if dst == 1 & StateAbbr == "AZ"
replace time_zone_offset = -5 if dst == 0 & time_zone == "Eastern"
replace time_zone_offset = -6 if dst == 0 & time_zone == "Central"
replace time_zone_offset = -7 if dst == 0 & time_zone == "Mountain"
replace time_zone_offset = -8 if dst == 0 & time_zone == "Pacific"

label variable dst              "1 for 03/11/2018 <= date < 11/04/2018; 0 for dates under standard time"
label variable time_zone_offset "time zone offset, by time zone and DST/ST"

solar_calculator, date(calendar_date) tz_offset(time_zone_offset) latitude(county_lat) longitude(county_lon) 
gen double daylight_duration = sunlight_duration/60
label variable daylight_duration "hours of daylight: sunset time - sunrise time"
drop sun_ra sun_dec solar_noon sunlight_duration

sort CountyFIPS calendar_date
codebook, compact
compress
save "data_clean\solar\\`pgm'.dta", replace

log close
exit